﻿Imports System.Net.Mail

Public Class frmElevesMAJ

    Private szSQL As String
    Private rs As ADODB.Recordset
    Dim pNoLigne As Integer
    Dim oldFormule As String = ""
    Dim oldEnregistre_le As String = ""

    Private Sub FrmElevesMAJ_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        InitComboSocietes(CB_SOCIETE)
        InitComboPermis(CB_PERMIS)
        InitComboAgences(CB_AGENCES)
        InitComboPays(CB_NATIONALITE)
        InitComboPays(CB_PAYS_NAIS)
        InitComboFilieres(CB_FILIERE)
        InitComboStatuts(CB_STATUT)
        LB_CHOIX.SelectedIndex = 0

        ChkAvecRDVannule.Checked = False
        EtatCivilToScreen()
        InscriptionToScreen()
        PermisToScreen(0)
        GB_PERMIS_OBTENU.Visible = False
        ExamensToScreen()
        SuivisToScreen(0)
        PayeurToScreen(0)
        ComptesToScreen(0)

        TC_DETAILS.SelectedTab = TP_ETAT_CIVIL
        TE_NOM.Focus()

        If gUserAcces < 9 Then
            If gUserAcces < 6 Then
                CB_AGENCES.Enabled = False
                CB_STATUT.Enabled = False
            Else
                CB_AGENCES.Enabled = True
                CB_STATUT.Enabled = True
            End If
            TE_FIN_CONTRAT.Enabled = False
            TE_FIN_FORFAIT_CODE.Enabled = False
            chkENTREPRISE.Enabled = False
            TE_ENCOURS.Enabled = False
            lb_AVANCE.Visible = False
            TE_AVANCE.Visible = False
            TE_CLOTURE_LE.ReadOnly = True
            Label10.Visible = False
            TE_SOLDE_REEL.Visible = False
        Else
            TE_FIN_CONTRAT.Enabled = True
            TE_FIN_FORFAIT_CODE.Enabled = True
            chkENTREPRISE.Enabled = True
            TE_ENCOURS.Enabled = True
            CB_AGENCES.Enabled = True
            lb_AVANCE.Visible = True
            TE_AVANCE.Visible = True
            TE_CLOTURE_LE.ReadOnly = False
            Label10.Visible = True
            TE_SOLDE_REEL.Visible = True
        End If

    End Sub

    Private Sub EtatCivilToScreen()
        ' création de fiche
        If gCD_CLI = 0 Then
            ClearTextBoxes(Me)
            CB_QUALITE.SelectedIndex = 0
            CB_PAYS_NAIS.SelectedIndex = CB_PAYS_NAIS.FindString("FR")
            CB_NATIONALITE.SelectedIndex = CB_NATIONALITE.FindString("FR")
            TE_DEPT_NAIS.Text = "69"
            CB_STATUT.SelectedIndex = 0
            TE_DATE_NAIS.Value = Now
            TE_DATE_INSC.Text = Now.ToShortDateString
            chkENTREPRISE.Checked = False
            oldFormule = ""
            oldEnregistre_le = ""
            Exit Sub
        End If

        ' Lire la fiche Elève
        szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            ' est-ce une ativité ? oui quitter
            If rs.Fields("agence").Value = 0 Then
                Exit Sub
            End If
            CB_QUALITE.SelectedIndex = CB_QUALITE.FindString(rs.Fields("civilite").Value.ToString)
            CB_AGENCES.SelectedIndex = rs.Fields("agence").Value - 1
            TE_CD_CLI.Text = Format(rs.Fields("cd_cli").Value, "000000")
            TE_NOM.Text = rs.Fields("nom").Value
            TE_PRENOM.Text = rs.Fields("prenom").Value
            TE_NOM_JF.Text = nv(rs.Fields("nom_jf").Value)
            TE_ADR_NO.Text = nv(rs.Fields("adresse_no_voie").Value)
            TE_ADR_EXT.Text = nv(rs.Fields("adresse_extension").Value)
            TE_ADR_TYPE.Text = nv(rs.Fields("adresse_type_voie").Value)
            TE_ADR_VOIE.Text = nv(rs.Fields("adresse_nom_voie").Value)
            TE_ADRESSE2.Text = nv(rs.Fields("adresse2").Value)
            TE_CODEPOSTE.Text = nv(rs.Fields("codepost").Value)
            TE_VILLE.Text = nv(rs.Fields("ville").Value)
            TE_TEL.Text = nv(rs.Fields("tel").Value)
            TE_TEL2.Text = nv(rs.Fields("tel2").Value)
            TE_TEL3.Text = nv(rs.Fields("tel3").Value)
            TE_TEL4.Text = nv(rs.Fields("tel4").Value)
            TE_PROFESSION.Text = nv(rs.Fields("profession").Value)
            TE_EMAIL.Text = nv(rs.Fields("email").Value)
            CB_NATIONALITE.SelectedIndex = CB_NATIONALITE.FindString(rs.Fields("nation").Value)
            TE_DATE_NAIS.Text = IfDate(rs.Fields("date_nais").Value)
            TE_LIEU_NAIS.Text = nv(rs.Fields("lieu_nais").Value)
            TE_DEPT_NAIS.Text = rs.Fields("dept_nais").Value
            CB_PAYS_NAIS.SelectedIndex = CB_PAYS_NAIS.FindString(rs.Fields("pays_nais").Value)
            If rs.Fields("type_entreprise").Value = "E" Then
                chkENTREPRISE.Checked = True
            Else
                chkENTREPRISE.Checked = False
            End If
            TE_NOM_ENTREP.Text = nv(rs.Fields("nom_entreprise").Value)
            TE_NO_FACT.Text = nv(rs.Fields("no_facture").Value)
            TE_ENCOURS.Text = nv(rs.Fields("encours_societe").Value)
            CB_SOCIETE.SelectedIndex = CB_SOCIETE.FindString(rs.Fields("societe").Value)
            TE_DATE_INSC.Text = Format(rs.Fields("cree_le").Value, "dd/MM/yyyy")
            TE_CLOTURE_LE.Text = IfDate(rs.Fields("cloture_le").Value)
            TE_DERN_OPER_LE.Text = IfDate(rs.Fields("dern_ope_le").Value)
            CB_STATUT.SelectedIndex = CB_STATUT.FindString(Format(rs.Fields("statut").Value, "00"))
            TE_MODIFIE_LE.Text = setModifiedBy(rs.Fields("cree_le").Value, rs.Fields("modif_le").Value, rs.Fields("cd_uti").Value)
            Me.Text = "Fiche : " & gCD_CLI & " - " & rs.Fields("nompren").Value & " - " & nv(rs.Fields("formule").Value)
            ' mémoriser ancien formule
            oldFormule = nv(rs.Fields("formule").Value)
        End If
        rs.Close()

    End Sub

    Private Sub InscriptionToScreen()

        If gCD_CLI = 0 Then
            CB_PERMIS.SelectedIndex = CB_PERMIS.FindString("B")
            CB_FILIERE.SelectedIndex = CB_FILIERE.FindString("B")
            CB_NIVEAU.SelectedIndex = 0
            ' Clear all CheckBox in Tab page INSCRIPTION
            For Each tp In TP_INSCRIPTION.Controls
                If TypeOf tp Is GroupBox Then
                    For Each element In tp.controls
                        If TypeOf element Is CheckBox Then
                            Dim chk As CheckBox = element
                            chk.Checked = False
                        End If
                    Next
                End If
            Next

            chkENTREPRISE.Checked = False
            ChkNePasRelancer.Checked = False
            chkNePasSpammer.Checked = False
            Exit Sub
        End If
        ' Lire la fiche Elève
        szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            CB_PERMIS.SelectedIndex = CB_PERMIS.FindString(rs.Fields("permis_demande").Value.ToString)
            CB_FILIERE.SelectedIndex = CB_FILIERE.FindString(rs.Fields("filiere").Value.ToString)
            CB_NIVEAU.SelectedIndex = CB_NIVEAU.FindString(rs.Fields("niveau").Value.ToString)
            TE_DATE_PD.Text = IfDate(rs.Fields("date_pd").Value)
            TE_FORMULE.Text = nv(rs.Fields("formule").Value)
            TE_FIN_CONTRAT.Value = rs.Fields("fin_contrat_le").Value
            TE_FIN_FORFAIT_CODE.Text = IfDate(rs.Fields("fin_forfait_code_le").Value)
            ' si date de fin forfait code est saisie ?
            If TE_FIN_FORFAIT_CODE.TextLength > 0 Then
                If DateDiff(DateInterval.Day, Now, CDate(TE_FIN_FORFAIT_CODE.Text)) < 0 Then
                    MsgBox("Le forfait code de l'élève est expiré.", MsgBoxStyle.Information)
                End If
            End If
            TE_STAGE_DEBUT.Text = IfDate(rs.Fields("stage_debut").Value)
            TE_STAGE_FIN.Text = IfDate(rs.Fields("stage_fin").Value)
            ChkLettreMax.Checked = rs.Fields("lettre_max").Value
            ChkParCredit.Checked = rs.Fields("parcredit").Value
            chkTransfert.Checked = rs.Fields("transfert").Value
            chkEleveGarde02.Checked = rs.Fields("elevegarde02").Value
            ChkNePasRelancer.Checked = rs.Fields("NePasRelancer").Value
            chkNePasSpammer.Checked = rs.Fields("NePasSpammer").Value
            TE_CD_MON.Text = nv(rs.Fields("cd_mon").Value)
            TE_ENREGISTRE_LE.Text = IfDate(rs.Fields("dossier_date").Value)
            oldEnregistre_le = TE_ENREGISTRE_LE.Text
            TE_VALIDE_JSQA.Text = IfDate(rs.Fields("dossier_valide").Value)
            TE_VISITE_MED_LE.Text = IfDate(rs.Fields("visite_med_le").Value)
            TE_VISITE_MED_FIN_LE.Text = IfDate(rs.Fields("visite_med_fin_le").Value)
            TE_COMPLET_LE.Text = IfDate(rs.Fields("complet_le").Value)

            TE_DOSSIER_NO.Text = nv(rs.Fields("dossier_no").Value)
            ChkPhoto.Checked = rs.Fields("photo").Value
            ChkCopiePermis.Checked = rs.Fields("copiepermis").Value
            ChkCNI.Checked = rs.Fields("cni").Value
            ChkAutorisationParent.Checked = rs.Fields("AutorisationParent").Value
            ChkImprime02.Checked = rs.Fields("Imprime02").Value
            ChkAssurance.Checked = rs.Fields("Assurance").Value
            ChkAttestationRecens.Checked = rs.Fields("AttestationRecens").Value
            ChkParticipeDefense.Checked = rs.Fields("ParticipeDefense").Value
            chkRefPrefecture.Checked = rs.Fields("reference_prefecture").Value
            ChkPermisEtranger.Checked = rs.Fields("permis_etranger").Value
            ChkAnnulationPermis.Checked = rs.Fields("annulation_permis").Value
            ChkConduiteARepasser.Checked = rs.Fields("conduite_repasser").Value
            CB_CAS_PAP.SelectedItem = CB_CAS_PAP.FindString(rs.Fields("cas_PAP").Value.ToString)
            TE_PERMIS_DELIVRE_LE.Text = IfDate(rs.Fields("permis_delivre_le").Value)
            TE_PERMIS_DELIVRE_PAR.Text = nv(rs.Fields("permis_delivre_par").Value)
            TE_INFRACTION_LE.Text = IfDate(rs.Fields("infraction_le").Value)
            If IsDate(rs.Fields("infraction_a").Value) Then
                TE_INFRACTION_A.Text = Format(rs.Fields("infraction_a").Value, "HH:mm")
            Else
                TE_INFRACTION_A.Clear()
            End If
            TE_INFRACTION_LIEU.Text = nv(rs.Fields("infraction_lieu").Value)
            TE_REP_NOM.Text = nv(rs.Fields("representant_nom").Value)
            TE_REP_PRENOM.Text = nv(rs.Fields("representant_prenom").Value)
            TE_REP_DNAISS.Text = nv(rs.Fields("representant_dnaiss").Value)
            TE_REP_ADRESSE.Text = nv(rs.Fields("representant_adresse").Value)
            TE_REP_CP.Text = nv(rs.Fields("representant_cp").Value)
            TE_REP_VILLE.Text = nv(rs.Fields("representant_ville").Value)
            If gUserAcces = 9 Then
                TE_VALEUR_HC.Text = "HC = " & nv(rs.Fields("valeur_hc").Value) & ", Circuit = " & nv(rs.Fields("valeur_cc").Value)
            End If
        End If
        rs.Close()

    End Sub

    Private Sub PermisToScreen(ByVal ix As Integer)
        Dim nb As Integer = 0

        ' effacer la listview
        LV_PERMIS.Items.Clear()
        ' creation ?
        If gCD_CLI = 0 Then
            Exit Sub
        Else
            ' Lire les permis déjà obtenus par ordre descroissant
            szSQL = "CALL spGet_ELEVES_PERMIS_byCD_CLI(" & gCD_CLI & ")"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            Do While Not rs.EOF
                LV_PERMIS.Items.Add(rs.Fields("permis").Value)
                LV_PERMIS.Items(nb).SubItems.Add(IfDate(rs.Fields("obtenu_le").Value))
                LV_PERMIS.Items(nb).SubItems.Add(nv(rs.Fields("lieu").Value))
                LV_PERMIS.Items(nb).SubItems.Add(nv(rs.Fields("numero").Value))
                LV_PERMIS.Items(nb).SubItems.Add(rs.Fields("cpm").Value)
                LV_PERMIS.Items(nb).SubItems.Add(IfDate(rs.Fields("perime_le").Value))
                LV_PERMIS.Items(nb).SubItems.Add(nv(rs.Fields("no_ligne").Value))
                rs.MoveNext()
                nb = nb + 1
            Loop
            ' Close the recordset AND connection.
            rs.Close()

            ' information sur les permis obttenus
            ' Lire la fiche Elève
            szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            If rs.RecordCount > 0 Then
                TE_NO_PO1.Text = nv(rs.Fields("no_po1").Value)
                TE_PERM_PO1.Text = nv(rs.Fields("perm_po1").Value)
                TE_DATE_PO1.Text = nv(rs.Fields("date_po1").Value)
                TE_LIEU_PO1.Text = nv(rs.Fields("lieu_po1").Value)
            End If

            setIndice(ix, LV_PERMIS)
        End If

    End Sub

    Private Sub ExamensToScreen()
        Dim nb As Integer = 0

        If gCD_CLI = 0 Then
            TE_TR_T_NB.Value = 0
            TE_TR_P_NB.Value = 0
            TE_TR_R_NB.Value = 0
            ' effacer la listview
            LV_EXAMENS.Items.Clear()
            Exit Sub
        End If
        ' Lire la fiche Elève
        szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            TE_TR_T_NB.Text = rs.Fields("tr_t_nb").Value
            TE_TR_P_NB.Text = rs.Fields("tr_p_nb").Value
            TE_TR_R_NB.Text = rs.Fields("tr_r_nb").Value
            TE_TR_T_DATE.Text = IfDate(rs.Fields("tr_t_date").Value)
            TE_TR_P_DATE.Text = IfDate(rs.Fields("tr_p_date").Value)
            TE_TR_R_DATE.Text = IfDate(rs.Fields("tr_r_date").Value)
            TE_TR_T_OK.Text = IfDate(rs.Fields("tr_t_ok").Value)
            TE_TR_P_OK.Text = IfDate(rs.Fields("tr_p_ok").Value)
            TE_TR_R_OK.Text = IfDate(rs.Fields("tr_r_ok").Value)
        End If
        rs.Close()

        ' effacer la listview
        LV_EXAMENS.Items.Clear()

        ' Lire la table EXAMENS_AFF
        szSQL = "CALL spGet_EXAMENS_AFF_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            LV_EXAMENS.Items.Add(rs.Fields("date").Value)
            LV_EXAMENS.Items(nb).SubItems.Add(Format(rs.Fields("heure").Value, "HH:mm"))
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("permis").Value)
            ' si résultat saisie, affiche son intitulé, sinon rien
            If rs.Fields("resultat").Value Then
                LV_EXAMENS.Items(nb).SubItems.Add(getLibelleByCode("P_STATUTS_EXAM", rs.Fields("resultat").Value))
            Else
                LV_EXAMENS.Items(nb).SubItems.Add(" ")
            End If
            If rs.Fields("valide").Value Then    ' si examen validé, colorer la ligne en vert
                LV_EXAMENS.Items(nb).ForeColor = Color.Green
            End If
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("no_exa").Value)
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("unite").Value)
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("indice").Value)
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("agence").Value & " " & rs.Fields("cd_uti").Value)
            LV_EXAMENS.Items(nb).SubItems.Add(rs.Fields("lieu").Value)
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub SuivisToScreen(ByVal ix As Integer)
        Dim nb As Integer = 0

        ' effacer la listview
        LV_SUIVI.Items.Clear()

        ' creation ?
        If gCD_CLI = 0 Then
            Exit Sub
        Else
            ' Lire les suivis de l'élève
            szSQL = "CALL spGet_ELEVES_SUI_byCD_CLI('SUIVI'," & gCD_CLI & ")"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            Do While Not rs.EOF
                LV_SUIVI.Items.Add(Format(rs.Fields("cree_le").Value, "dd/MM/yyyy"))
                LV_SUIVI.Items(nb).SubItems.Add(rs.Fields("type_rel").Value)
                LV_SUIVI.Items(nb).SubItems.Add(rs.Fields("moyen_rel").Value)
                LV_SUIVI.Items(nb).SubItems.Add(nv(rs.Fields("resultat").Value))
                LV_SUIVI.Items(nb).SubItems.Add(rs.Fields("cd_uti").Value)
                LV_SUIVI.Items(nb).SubItems.Add(rs.Fields("no_ligne").Value)
                rs.MoveNext()
                nb = nb + 1
            Loop
            rs.Close()
            setIndice(ix, LV_SUIVI)
        End If

    End Sub

    Private Sub PayeurToScreen(ByVal ix As Integer)
        Dim nb As Integer = 0

        ' effacer la listview
        LV_PAYEUR.Items.Clear()

        ' creation ?
        If gCD_CLI = 0 Then
            Exit Sub
        Else
            ' Lire les suivis du payeur
            szSQL = "CALL spGet_ELEVES_SUI_byCD_CLI('PAYEUR'," & gCD_CLI & ")"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            Do While Not rs.EOF
                LV_PAYEUR.Items.Add(Format(rs.Fields("cree_le").Value, "dd/MM/yyyy"))
                LV_PAYEUR.Items(nb).SubItems.Add(rs.Fields("type_rel").Value)
                LV_PAYEUR.Items(nb).SubItems.Add(rs.Fields("moyen_rel").Value)
                LV_PAYEUR.Items(nb).SubItems.Add(nv(rs.Fields("resultat").Value))
                LV_PAYEUR.Items(nb).SubItems.Add(rs.Fields("cd_uti").Value)
                LV_PAYEUR.Items(nb).SubItems.Add(rs.Fields("no_ligne").Value)
                rs.MoveNext()
                nb = nb + 1
            Loop
            rs.Close()
            setIndice(ix, LV_PAYEUR)
        End If

    End Sub

    Private Sub ComptesToScreen(ByVal ix As Integer)
        Dim nb As Integer = 0
        Dim noLigneMax As Integer = 0
        Dim ixMax As Integer = 0

        If gCD_CLI = 0 Then
            ' effacer la listview
            LV_COMPTE.Items.Clear()
            Exit Sub
        End If
        ' Lire la fiche Elève
        szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            TE_DEBIT.Text = rs.Fields("debit").Value
            TE_CREDIT.Text = rs.Fields("credit").Value
            TE_MTVAL.Text = rs.Fields("mtval").Value
            TE_AVANCE.Text = rs.Fields("avance").Value
            ' Solde réel = total crédit – total débit + avance heures + Avance présentations
            TE_SOLDE_REEL.Text = rs.Fields("credit").Value - rs.Fields("debit").Value + rs.Fields("avance").Value + rs.Fields("avance_pres").Value
            ' Solde prévu = total crédit – total débit 
            TE_SOLDE_PREVU.Text = rs.Fields("credit").Value - rs.Fields("debit").Value
            ' info PAYEUR
            TE_HEURES_ESTIMEES.Value = rs.Fields("heures_estimees").Value
            TE_HEURES_REVUES.Value = rs.Fields("heures_revues").Value
            TE_HEURES_PREVUES.Text = rs.Fields("heures_prevues").Value
            TE_HEURES_PRISES.Text = rs.Fields("heures_prises").Value
            TE_HEURES_PREVUES2.Text = rs.Fields("heures_prevues").Value
            TE_HEURES_PRISES2.Text = rs.Fields("heures_prises").Value
            chkPAYEUR_SUIVI.Checked = rs.Fields("payeur_suivi").Value
            TE_PAYEUR_NOM.Text = nv(rs.Fields("payeur_nom").Value)
            TE_PAYEUR_ADRESSE.Text = nv(rs.Fields("payeur_adresse").Value)
            TE_PAYEUR_CP.Text = nv(rs.Fields("payeur_cp").Value)
            TE_PAYEUR_VILLE.Text = nv(rs.Fields("payeur_ville").Value)
            TE_PAYEUR_EMAIL.Text = nv(rs.Fields("payeur_email").Value)
            TE_PAYEUR_TEL.Text = nv(rs.Fields("payeur_tel").Value)
        End If
        rs.Close()

        If CInt(TE_SOLDE_PREVU.Text) < 0 Then
            TE_SOLDE_PREVU.ForeColor = Color.Red
        Else
            TE_SOLDE_PREVU.ForeColor = Color.Blue
        End If
        If CInt(TE_SOLDE_REEL.Text) < 0 Then
            TE_SOLDE_REEL.ForeColor = Color.Red
        Else
            TE_SOLDE_REEL.ForeColor = Color.Blue
        End If

        ' effacer la listview
        LV_COMPTE.Items.Clear()
        ' Lire les comptes de l'élève
        szSQL = "CALL spGet_ELEVES_CPT_byCD_CLI(" & gCD_CLI & "," & Val(ChkAvecRDVannule.Checked) & ",'')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            LV_COMPTE.Items.Add(rs.Fields("date").Value)
            If rs.Fields("noplan").Value > 0 Then
                LV_COMPTE.Items(nb).SubItems.Add((rs.Fields("noplan").Value) & ":00")
            Else
                LV_COMPTE.Items(nb).SubItems.Add(" ")
            End If
            LV_COMPTE.Items(nb).SubItems.Add(nv(rs.Fields("cd_mon").Value))
            LV_COMPTE.Items(nb).SubItems.Add(nv(rs.Fields("intitule").Value))
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("qte").Value))
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("debit").Value))
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("credit").Value))
            If rs.Fields("mode_regl").Value Then
                LV_COMPTE.Items(nb).SubItems.Add(Mid(getLibelleByCode("P_MODE_REGL", rs.Fields("mode_regl").Value), 1, 3))
            Else
                LV_COMPTE.Items(nb).SubItems.Add("")
            End If
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("agence").Value & " " & rs.Fields("cd_uti").Value)
            LV_COMPTE.Items(nb).SubItems.Add(IfDate(rs.Fields("date_valeur").Value))
            If rs.Fields("date_valeur").Value.ToString.Length Then
                LV_COMPTE.Items(nb).ForeColor = Color.Green
            End If
            If rs.Fields("fin_reservation").Value.ToString.Length Then
                LV_COMPTE.Items(nb).ForeColor = Color.DarkViolet
            End If
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("mtval").Value)
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("no_ligne").Value)
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("lettre").Value))
            ' save position du no de ligne max
            If rs.Fields("no_ligne").Value > noLigneMax Then
                ixMax = nb
                noLigneMax = rs.Fields("no_ligne").Value
            End If
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()
        ' positionner le curseur sur la dernière ligne créée ?
        If ix = 9999 Then
            setIndice(ixMax, LV_COMPTE)
        Else
            setIndice(ix, LV_COMPTE)
        End If

    End Sub

    Private Sub LV_SUIVI_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_SUIVI.DoubleClick

        If LV_SUIVI.SelectedItems.Count > 0 Then
            ' afficher les détails de la ligne NoLigne
            frmElevesSuivi.pListe = "SUIVI"
            frmElevesSuivi.pNo_ligne = LV_SUIVI.SelectedItems(0).SubItems(5).Text
            frmElevesSuivi.ShowDialog()
            ' réaffiche la viewlist
            SuivisToScreen(LV_SUIVI.SelectedItems(0).Index)
        End If

    End Sub

    Private Sub LV_SUIVI_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_SUIVI.KeyDown

        Select Case e.KeyCode
            Case Keys.Enter         ' Enter = DoubleClick
                LV_SUIVI_DoubleClick(sender, e)

            Case Keys.Insert        ' Insert = ' Ajouter un élève
                BO_ADD_SUIVI_Click(sender, e)

        End Select
    End Sub

    Private Sub LV_COMPTE_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_COMPTE.DoubleClick

        If LV_COMPTE.SelectedItems.Count > 0 Then
            ' ligne Crédit ou débit ?
            If LV_COMPTE.SelectedItems(0).SubItems(6).Text.Length = 0 Then
                ' affiche le formulaire DEBIT
                frmElevesDebit.pNo_ligne = LV_COMPTE.SelectedItems(0).SubItems(11).Text
                frmElevesDebit.ShowDialog()
                ' réaffiche la viewlist
                ComptesToScreen(LV_COMPTE.SelectedItems(0).Index)
            Else
                ' affiche le formulaire CREDIT
                frmElevesCredit.pNo_ligne = LV_COMPTE.SelectedItems(0).SubItems(11).Text
                frmElevesCredit.ShowDialog()
                ' réaffiche la viewlist
                ComptesToScreen(LV_COMPTE.SelectedItems(0).Index)
            End If
        End If
    End Sub

    Private Sub LV_COMPTE_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_COMPTE.KeyDown
        Select Case e.KeyCode
            Case Keys.Enter         ' Enter = DoubleClick
                LV_COMPTE_DoubleClick(sender, e)

            Case Keys.Insert        ' Insert = ' Ajouter un credit ou debit
                BO_ADD_Click(sender, e)
        End Select

    End Sub

    Private Sub LB_CHOIX_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LB_CHOIX.DoubleClick

        If LB_CHOIX.SelectedIndex = 0 Then
            ' affiche le formulaire CREDIT
            frmElevesCredit.pNo_ligne = 0
            frmElevesCredit.ShowDialog()
        Else
            If gUserAcces < 9 Then
                ' controler le solde de l'élève si user non admin
                If ControlerSolde("REEL", TE_CD_CLI.Text, "") = -1 Then
                    Exit Sub
                End If
            End If
            If ControlerEleves("DEB", gCD_CLI) = False Then
                Exit Sub
            End If
            ' affiche le formulaire DEBIT
            frmElevesDebit.pNo_ligne = 0
            frmElevesDebit.ShowDialog()
            ' réaffiche l'onglet inscription
            InscriptionToScreen()
        End If

        GB_CHOIX.Visible = False
        ' réaffiche la viewlist
        ComptesToScreen(9999)

    End Sub

    Private Sub LB_CHOIX_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LB_CHOIX.KeyDown
        Select Case e.KeyCode
            Case Keys.Enter         ' Enter = DoubleClick
                LB_CHOIX_DoubleClick(sender, e)

            Case Keys.Escape
                GB_CHOIX.Visible = False

        End Select
    End Sub

    Private Sub Genere_INSCRIPTION()

        ' maj éventuelles des dates
        szSQL = ""
        If IsDate(TE_ENREGISTRE_LE.Text) Then
            szSQL = szSQL & ",dossier_date='" & Format(CDate(TE_ENREGISTRE_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",dossier_date=NULL"
        End If
        If IsDate(TE_VISITE_MED_LE.Text) Then
            szSQL = szSQL & ",visite_med_le='" & Format(CDate(TE_VISITE_MED_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",visite_med_le=NULL"
        End If
        If IsDate(TE_VISITE_MED_FIN_LE.Text) Then
            szSQL = szSQL & ",visite_med_fin_le='" & Format(CDate(TE_VISITE_MED_FIN_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",visite_med_fin_le=NULL"
        End If
        If IsDate(TE_FIN_FORFAIT_CODE.Text) Then
            szSQL = szSQL & ",fin_forfait_code_le='" & Format(CDate(TE_FIN_FORFAIT_CODE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",fin_forfait_code_le=NULL"
        End If
        If IsDate(TE_CLOTURE_LE.Text) Then
            szSQL = szSQL & ",cloture_le='" & Format(CDate(TE_CLOTURE_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",cloture_le=NULL"
        End If
        If IsDate(TE_PERMIS_DELIVRE_LE.Text) Then
            szSQL = szSQL & ",permis_delivre_le='" & Format(CDate(TE_PERMIS_DELIVRE_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",permis_delivre_le=NULL"
        End If
        If IsDate(TE_INFRACTION_LE.Text) Then
            szSQL = szSQL & ",infraction_le='" & Format(CDate(TE_INFRACTION_LE.Text), "yyyy/MM/dd") & "'"
        Else
            szSQL = szSQL & ",infraction_le=NULL"
        End If
        If IsDate(TE_INFRACTION_A.Text) Then
            szSQL = szSQL & ",infraction_a='" & Format(CDate(TE_INFRACTION_A.Text), "HH:mm") & "'"
        Else
            szSQL = szSQL & ",infraction_a=NULL"
        End If
        If szSQL.Length > 0 Then
            szSQL = "UPDATE ELEVES SET " & Mid(szSQL, 2, szSQL.Length) & " WHERE cd_cli=" & gCD_CLI
            ExecQuery(ctDSN_AEM, szSQL)
        End If

        ' génére les écritures d'inscription selon la formule si chgmt de formule
        If TE_FORMULE.TextLength > 0 And TE_FORMULE.Text <> oldFormule Then
            szSQL = "CALL spINS_ELEVES_DEBIT_INSCRIPTION('" & TE_FORMULE.Text & "'," & gCD_CLI & ",'" & _
                     Format(CDate(TE_DATE_INSC.Text), "yyyy/MM/dd") & "'," & gUserAgence & ",'" & gUserCode & "')"
                ExecQuery(ctDSN_AEM, szSQL)
        End If
        ' mémoriser que l'inscription est bien générée
        oldFormule = TE_FORMULE.Text

        ' valorise les écritures d'inscription selon la formule si dossier enregistré à la préfecture
        If TE_ENREGISTRE_LE.TextLength > 0 And TE_ENREGISTRE_LE.Text <> oldEnregistre_le Then
            szSQL = "CALL spUPD_ELEVES_VALORISER('" & TE_FORMULE.Text & "'," & gCD_CLI & ")"
            ExecQuery(ctDSN_AEM, szSQL)
        End If
        ' mémoriser que l'inscription est bien valorisée
        oldEnregistre_le = TE_ENREGISTRE_LE.Text

    End Sub

    Private Sub BO_EXAMENS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_EXAMENS.Click

        If IsExamens_OK() Then
            szSQL = "UPDATE ELEVES SET tr_t_nb=" & TE_TR_T_NB.Value & ",tr_p_nb=" & TE_TR_P_NB.Value & ",tr_r_nb=" & TE_TR_R_NB.Value
            If IsDate(TE_TR_T_DATE.Text) Then
                szSQL = szSQL & ",tr_t_date='" & Format(CDate(TE_TR_T_DATE.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_t_date=NULL"
            End If
            If IsDate(TE_TR_P_DATE.Text) Then
                szSQL = szSQL & ",tr_p_date='" & Format(CDate(TE_TR_P_DATE.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_p_date=NULL"
            End If
            If IsDate(TE_TR_R_DATE.Text) Then
                szSQL = szSQL & ",tr_r_date='" & Format(CDate(TE_TR_R_DATE.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_r_date=NULL"
            End If
            If IsDate(TE_TR_T_OK.Text) Then
                szSQL = szSQL & ",tr_t_ok='" & Format(CDate(TE_TR_T_OK.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_t_ok=NULL"
            End If
            If IsDate(TE_TR_P_OK.Text) Then
                szSQL = szSQL & ",tr_p_ok='" & Format(CDate(TE_TR_P_OK.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_p_ok=NULL"
            End If
            If IsDate(TE_TR_R_OK.Text) Then
                szSQL = szSQL & ",tr_r_ok='" & Format(CDate(TE_TR_R_OK.Text), "yyyy/MM/dd") & "'"
            Else
                szSQL = szSQL & ",tr_r_ok=NULL"
            End If
            szSQL = szSQL & " WHERE cd_cli=" & gCD_CLI
            If ExecQuery(ctDSN_AEM, szSQL) Then
                MsgBox("Fiche enregistré : " & gCD_CLI)
            End If
        End If

    End Sub

    Private Sub BO_PERMIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_PERMIS.Click
        Dim szDate As String

        If TE_LIEU.TextLength = 0 Or TE_PERMIS.TextLength = 0 Then
            MsgBox("Catégorie et lieu du permis sont obligatoires", MsgBoxStyle.Critical)
            TE_PERMIS.Focus()
            Exit Sub
        End If
        If IsDate(TE_OBTENU_LE.Value) = False Then
            MsgBox("Date d'obtention incorrecte", MsgBoxStyle.Critical)
            TE_OBTENU_LE.Focus()
            Exit Sub
        End If
        If TE_PERIME_LE.TextLength > 0 Then
            If IsDate(TE_PERIME_LE.Text) = False Then
                MsgBox("Date de péremption incorrecte", MsgBoxStyle.Critical)
                TE_PERIME_LE.Focus()
                Exit Sub
            Else
                szDate = Format(CDate(TE_PERIME_LE.Text), "yyyy/MM/dd")
            End If
        Else
            szDate = TE_PERIME_LE.Text
        End If
        ' mise à jour 
        szSQL = "CALL spUpd_ELEVES_PERMIS(" & TE_NOLIG.Text & ",'" & Format(TE_OBTENU_LE.Value, "yyyy/MM/dd") & "','" & TE_PERMIS.Text & "','" & _
                TE_NUMERO.Text & "','" & TE_LIEU.Text & "'," & CInt(chkCPM.Checked) & ",'" & szDate & "','" & gUserCode & "')"
        If ExecQuery(ctDSN_AEM, szSQL) Then
            GB_PERMIS_OBTENU.Visible = False
            PermisToScreen(LV_PERMIS.SelectedItems(0).Index)
        End If

    End Sub

    Private Sub LV_PERMIS_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_PERMIS.DoubleClick

        If LV_PERMIS.SelectedItems.Count > 0 Then
            ' afficher les détails de la ligne NoLigne
            ' Lire l'enreg sélectionné
            pNoLigne = LV_PERMIS.SelectedItems(0).SubItems(6).Text
            szSQL = "CALL spGet_ELEVES_PERMIS_byNO_LIGNE(" & pNoLigne & ")"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            If rs.RecordCount > 0 Then
                TE_PERMIS.Text = rs.Fields("permis").Value
                chkCPM.Checked = rs.Fields("cpm").Value
                TE_OBTENU_LE.Value = rs.Fields("obtenu_le").Value
                TE_LIEU.Text = nv(rs.Fields("lieu").Value)
                TE_NUMERO.Text = nv(rs.Fields("numero").Value)
                TE_PERIME_LE.Text = IfDate(rs.Fields("perime_le").Value)
                TE_NOLIG.Text = rs.Fields("no_ligne").Value
                TE_MODIFIE_LE2.Text = setModifiedBy(rs.Fields("cree_le").Value, rs.Fields("modif_le").Value, rs.Fields("cd_uti").Value)
            End If
            rs.Close()
            GB_PERMIS_OBTENU.Visible = True
            TE_PERMIS.Focus()
        End If

    End Sub

    Private Sub LV_PERMIS_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_PERMIS.KeyDown

        Select Case e.KeyCode
            Case Keys.Enter         ' Enter = DoubleClick
                LV_PERMIS_DoubleClick(sender, e)

            Case Keys.Insert        ' Insert = ' Ajouter un élève
                BO_ADD_PERMIS_Click(sender, e)

            Case Keys.Delete        ' Suppr = ' suppression
                BO_DELETE_PERMIS_Click(sender, e)

        End Select
    End Sub

    Private Sub BO_ANNULER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ANNULER.Click
        GB_PERMIS_OBTENU.Visible = False
    End Sub

    ' Contrôles de saisie de la fiche ELEVES
    Private Function IsSaisie_OK() As Boolean

        IsSaisie_OK = False

        ' si Elève = Mme, Nom de jeune fille obligatoire
        If CB_QUALITE.SelectedItem = "Mme" And TE_NOM_JF.TextLength = 0 Then
            MsgBox("Le nom de jeune fille est obligatoire", MsgBoxStyle.Critical)
            TE_NOM_JF.Focus()
            Exit Function
        End If
        ' Controle des zones obligatoires
        If TE_NOM.TextLength = 0 Or TE_PRENOM.TextLength = 0 Then
            MsgBox("Le nom et le prénom sont obligatoires", MsgBoxStyle.Critical)
            TE_NOM.Focus()
            Exit Function
        End If
        If TE_ADR_VOIE.TextLength = 0 Or TE_CODEPOSTE.TextLength = 0 Or TE_VILLE.TextLength = 0 Then
            MsgBox("L'adresse est obligatoire", MsgBoxStyle.Critical)
            TE_ADR_VOIE.Focus()
            Exit Function
        End If
        If TE_LIEU_NAIS.TextLength = 0 Or TE_DEPT_NAIS.TextLength = 0 Then
            MsgBox("Lieu et pays de naissance sont obligatoires", MsgBoxStyle.Critical)
            TE_LIEU_NAIS.Focus()
            Exit Function
        End If
        ' si email saisi, est-il valide ?
        If TE_EMAIL.TextLength > 0 And IsEmailValid(TE_EMAIL.Text) = False Then
            MsgBox("L'adresse email est incorrecte", MsgBoxStyle.Critical)
            TE_EMAIL.Focus()
            Exit Function
        End If
        ' si formule obligatoire
        If TE_FORMULE.TextLength = 0 Then
            MsgBox("La formule est obligatoire", MsgBoxStyle.Critical)
            TE_FORMULE.Focus()
            Exit Function
        End If
        ' si dossier enregistre
        If TE_ENREGISTRE_LE.TextLength > 0 Then
            If IsDate(TE_ENREGISTRE_LE.Text) = False Then
                MsgBox("Date d'enregistrement : format incorrect", MsgBoxStyle.Critical)
                TE_ENREGISTRE_LE.Focus()
                Exit Function
            End If
        End If
        If IsDate(TE_ENREGISTRE_LE.Text) = True And TE_DOSSIER_NO.Text.Length = 0 Then
            MsgBox("Numéro de dossier et date de dossier incohérents.", MsgBoxStyle.Critical)
            TE_ENREGISTRE_LE.Focus()
            Exit Function
        End If
        ' date visite medicale saisie ?
        If TE_VISITE_MED_LE.TextLength > 0 Then
            ' oui, controler la validite
            If IsDate(TE_VISITE_MED_LE.Text) = False Then
                MsgBox("Date visite médicale : format incorrect", MsgBoxStyle.Critical)
                TE_VISITE_MED_LE.Focus()
                Exit Function
            End If
            If IsDate(TE_VISITE_MED_FIN_LE.Text) = False Then
                MsgBox("Date visite médicale : format incorrect", MsgBoxStyle.Critical)
                TE_VISITE_MED_FIN_LE.Focus()
                Exit Function
            End If
        Else
            ' non, effacer la date de fin de validité
            TE_VISITE_MED_FIN_LE.Clear()
        End If
        If TE_CLOTURE_LE.TextLength > 0 Then
            If IsDate(TE_CLOTURE_LE.Text) = False Then
                MsgBox("Date de cloture : format incorrect", MsgBoxStyle.Critical)
                TE_CLOTURE_LE.Focus()
                Exit Function
            End If
        End If
        If TE_PERMIS_DELIVRE_LE.TextLength > 0 Then
            If IsDate(TE_PERMIS_DELIVRE_LE.Text) = False Then
                MsgBox("Date permis délivré : format incorrect", MsgBoxStyle.Critical)
                TE_PERMIS_DELIVRE_LE.Focus()
                Exit Function
            End If
        End If
        If TE_INFRACTION_LE.TextLength > 0 Then
            If IsDate(TE_INFRACTION_LE.Text) = False Then
                MsgBox("Date infraction : format incorrect", MsgBoxStyle.Critical)
                TE_INFRACTION_LE.Focus()
                Exit Function
            End If
        End If
        If TE_INFRACTION_A.TextLength > 0 Then
            If IsDate(TE_INFRACTION_A.Text) = False Then
                MsgBox("Format d'heure infraction incorrect (HH:mm).", MsgBoxStyle.Critical)
                Exit Function
            End If
        End If
        If TE_REP_DNAISS.TextLength > 0 Then
            If IsDate(TE_REP_DNAISS.Text) = False Then
                MsgBox("Date naissance représentant : format incorrect", MsgBoxStyle.Critical)
                Exit Function
            End If
        End If
        IsSaisie_OK = True

    End Function

    ' Contrôles de saisie de l'onglet EXAMENS
    Private Function IsExamens_OK() As Boolean

        IsExamens_OK = False
        ' Format des dates correct ?
        If TE_TR_T_DATE.TextLength > 0 And IsDate(TE_TR_T_DATE.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_T_DATE.Focus()
            Exit Function
        End If
        If TE_TR_P_DATE.TextLength > 0 And IsDate(TE_TR_P_DATE.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_P_DATE.Focus()
            Exit Function
        End If
        If TE_TR_R_DATE.TextLength > 0 And IsDate(TE_TR_R_DATE.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_R_DATE.Focus()
            Exit Function
        End If
        If TE_TR_T_OK.TextLength > 0 And IsDate(TE_TR_T_OK.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_T_OK.Focus()
            Exit Function
        End If
        If TE_TR_P_OK.TextLength > 0 And IsDate(TE_TR_P_OK.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_P_OK.Focus()
            Exit Function
        End If
        If TE_TR_R_OK.TextLength > 0 And IsDate(TE_TR_R_OK.Text) = False Then
            MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
            TE_TR_R_OK.Focus()
            Exit Function
        End If
        IsExamens_OK = True

    End Function

    Private Sub BO_FORMULES_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_FORMULES.Click

        'affiche la liste des Formules pour sélection
        frmTablesSearch.pTable = "formules"
        frmTablesSearch.pTitre = "Sélectionner une FORMULE"
        frmTablesSearch.pCode = TE_FORMULE.Text
        frmTablesSearch.pLibelle = CB_PERMIS.SelectedItem
        frmTablesSearch.ShowDialog()
        If frmTablesSearch.pCode.Length Then
            TE_FORMULE.Text = frmTablesSearch.pCode
        End If

        ' lire sa formule 
        szSQL = "CALL spGet_FORMULES('" & TE_FORMULE.Text & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            ' calculer la date de fin de validité du contrat
            TE_FIN_CONTRAT.Value = CDate(TE_DATE_INSC.Text).AddDays(rs.Fields("duree_cond").Value)
            ' si forfait inclut code, calculer la date de fin code
            If CInt(rs.Fields("duree_code").Value) > 0 Then
                TE_FIN_FORFAIT_CODE.Text = CDate(TE_DATE_INSC.Text).AddDays(rs.Fields("duree_code").Value)
            Else
                TE_FIN_FORFAIT_CODE.Clear()
            End If
            CB_FILIERE.SelectedIndex = CB_FILIERE.FindString(rs.Fields("filiere").Value)
        End If
        rs.Close()

    End Sub

    Private Sub BO_IMPRIMER_RECU_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_IMPRIMER_RECU.Click

        If LV_COMPTE.SelectedItems.Count > 0 Then
            ' la ligne sélectionnée est-elle un réglement ?
            If LV_COMPTE.SelectedItems(0).SubItems(7).Text.Length = 0 Then
                MsgBox("La ligne sélectionnée n'est pas un réglement.", MsgBoxStyle.Information, "Impression d'un reçu")
                Exit Sub
            End If
            ' imprimer le recu
            gNumero = LV_COMPTE.SelectedItems(0).SubItems(11).Text
            FrmViewReport.pCodeEtat = 18
            FrmViewReport.pParamRPT = ""
            FrmViewReport.ShowDialog()
        End If

    End Sub

    Private Sub CB_STATUT_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CB_STATUT.TextChanged

        ' raz date de cloture si fiche active
        If CB_STATUT.SelectedIndex = 0 Then
            TE_CLOTURE_LE.Clear()
        Else
            If TE_CLOTURE_LE.TextLength = 0 Then
                TE_CLOTURE_LE.Text = Now.ToShortDateString
            End If
        End If

    End Sub

    Private Sub ChkPhoto_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkPhoto.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub SetDateComplet()

        ' si pas de pièce manaquante, renseigner la date "dossier complet", sinon l'effacer
        If ChkPhoto.Checked Or ChkCNI.Checked Or ChkImprime02.Checked Or ChkCopiePermis.Checked Or chkRefPrefecture.Checked Or _
           ChkAutorisationParent.Checked Or ChkAssurance.Checked Or ChkAttestationRecens.Checked Or ChkParticipeDefense.Checked Then
            TE_COMPLET_LE.Clear()
        Else
            If TE_COMPLET_LE.TextLength = 0 Then
                TE_COMPLET_LE.Text = Now.ToShortDateString
            End If
        End If

    End Sub

    Private Sub ChkCNI_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkCNI.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkImprime02_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkImprime02.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkAttestationRecens_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkAttestationRecens.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkCopiePermis_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkCopiePermis.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkAutorisationParent_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkAutorisationParent.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkAssurance_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkAssurance.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub ChkParticipeDefense_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkParticipeDefense.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub chkRefPrefecture_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles chkRefPrefecture.CheckedChanged
        SetDateComplet()
    End Sub

    Private Sub TC_DETAILS_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TC_DETAILS.SelectedIndexChanged
        ' sélection d'un onglet autre que Eta-civil, Inscription, Permis à Point
        If TC_DETAILS.SelectedIndex > 2 Then
            ' création de fiche, save fiche
            If gCD_CLI = 0 Then
                If SreenToFiche() = False Then
                    TC_DETAILS.SelectedIndex = 0
                Else
                    ' réafficher les compte
                    ComptesToScreen(0)
                End If
            End If
        End If
    End Sub

    Private Function SreenToFiche() As Boolean

        SreenToFiche = False

        If TE_NOM.TextLength = 0 And TE_PRENOM.TextLength = 0 Then
            SreenToFiche = True
            Exit Function
        End If

        Dim szType As String
        If chkENTREPRISE.Checked = True Then
            szType = "E"
        Else
            szType = "P"
        End If

        If IsSaisie_OK() = False Then
            Exit Function
        End If

        ' mise à jour onglet ETAT-CIVIL
        szSQL = "CALL spUpd_ELEVES_ETATCIVIL(" & gCD_CLI & ",'" & CB_QUALITE.SelectedItem & "','" & nq(TE_NOM.Text) & "','" & nq(TE_PRENOM.Text) & "','" & _
            nq(TE_NOM_JF.Text) & "','" & nq(TE_ADR_NO.Text) & "','" & nq(TE_ADR_EXT.Text) & "','" & nq(TE_ADR_TYPE.Text) & "','" & nq(TE_ADR_VOIE.Text) & "','" & _
            nq(TE_ADRESSE2.Text) & "','" & TE_CODEPOSTE.Text & "','" & nq(TE_VILLE.Text) & "','" & TE_TEL.Text & "','" & _
            TE_TEL2.Text & "','" & TE_TEL3.Text & "','" & TE_TEL4.Text & "','" & nq(TE_PROFESSION.Text) & "','" & Mid(CB_NATIONALITE.SelectedItem, 1, 2) & "','" & Format(TE_DATE_NAIS.Value, "yyyy/MM/dd") & "','" & _
            nq(TE_LIEU_NAIS.Text) & "','" & TE_DEPT_NAIS.Text & "','" & Mid(CB_PAYS_NAIS.SelectedItem, 1, 2) & "','" & szType & "','" & TE_NOM_ENTREP.Text & "','" & _
            Mid(CB_SOCIETE.SelectedItem, 1, 1) & "'," & CInt(chkNePasSpammer.Checked) & "," & CInt(ChkNePasRelancer.Checked) & "," & TE_ENCOURS.Value & ",'" & TE_EMAIL.Text & "'," & _
            Mid(CB_STATUT.SelectedItem, 1, 2) & "," & CB_AGENCES.SelectedIndex + 1 & ",'" & gUserCode & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            gCD_CLI = rs.Fields("newcode").Value
        Else
            Exit Function
        End If
        ' mise à jour onglet INSCRIPTION
        szSQL = "CALL spUpd_ELEVES_INSCRIT(" & gCD_CLI & ",'" & CB_PERMIS.SelectedItem & "','" & TE_FORMULE.Text & "'," & _
            CInt(ChkLettreMax.Checked) & "," & CInt(ChkParCredit.Checked) & "," & CInt(chkTransfert.Checked) & "," & CInt(chkEleveGarde02.Checked) & ",'" & TE_DOSSIER_NO.Text & "'," & _
            CInt(ChkPhoto.Checked) & "," & CInt(ChkCNI.Checked) & "," & CInt(ChkImprime02.Checked) & "," & CInt(ChkAttestationRecens.Checked) & "," & _
            CInt(ChkCopiePermis.Checked) & "," & CInt(ChkAutorisationParent.Checked) & "," & CInt(ChkAssurance.Checked) & "," & CInt(ChkParticipeDefense.Checked) & "," & _
            CInt(chkRefPrefecture.Checked) & "," & CInt(ChkPermisEtranger.Checked) & "," & CInt(ChkAnnulationPermis.Checked) & "," & CInt(ChkConduiteARepasser.Checked) & ",'" & _
            CB_CAS_PAP.SelectedItem & "','" & TE_PERMIS_DELIVRE_PAR.Text & "','" & TE_INFRACTION_LIEU.Text & "','" & _
            Format(TE_FIN_CONTRAT.Value, "yyyy/MM/dd") & "','" & TE_CD_MON.Text & "','" & CB_FILIERE.SelectedItem & "','" & CB_NIVEAU.SelectedItem & "','" & _
            TE_REP_NOM.Text & "','" & TE_REP_PRENOM.Text & "','" & TE_REP_DNAISS.Text & "','" & TE_REP_ADRESSE.Text & "','" & TE_REP_CP.Text & "','" & TE_REP_VILLE.Text & "'," & _
            CInt(chkPAYEUR_SUIVI.Checked) & ",'" & TE_PAYEUR_NOM.Text & "','" & TE_PAYEUR_ADRESSE.Text & "','" & TE_PAYEUR_CP.Text & "','" & _
            TE_PAYEUR_VILLE.Text & "','" & TE_PAYEUR_EMAIL.Text & "','" & TE_PAYEUR_TEL.Text & "'," & TE_HEURES_ESTIMEES.Value & "," & TE_HEURES_REVUES.Value & ",'" & _
            gUserCode & "')"
        If ExecQuery(ctDSN_AEM, szSQL) = False Then
            Exit Function
        End If

        ' génère les écritures liées à l'inscription
        Genere_INSCRIPTION()
        SreenToFiche = True

    End Function

    Private Sub CB_PERMIS_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CB_PERMIS.SelectedIndexChanged
        If CB_PERMIS.Text = "EC" Or CB_PERMIS.Text = "C" Or CB_PERMIS.Text = "D" Or CB_PERMIS.Text = "BE" Then
            ' permis demandé GL -> société MFT
            CB_SOCIETE.SelectedIndex = CB_SOCIETE.FindString("T")
        End If
    End Sub

    Private Sub TE_OBTENU_LE_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TE_OBTENU_LE.ValueChanged
        TE_PERIME_LE.Text = TE_OBTENU_LE.Value.AddYears(5)
    End Sub

    Private Sub ChkAvecRDVannule_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAvecRDVannule.CheckedChanged
        ComptesToScreen(0)
    End Sub

    Private Sub BO_ADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ADD_CPT.Click
        ' utilisateur autorisé à ajouter une ligne ?
        If IsUserAuthorised(2) = False Then
            Exit Sub
        End If

        ' afficher la listbox CHOIX
        GB_CHOIX.Visible = True
        LB_CHOIX.Focus()
    End Sub

    Private Sub BO_ADD_SUIVI_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ADD_SUIVI.Click
        frmElevesSuivi.pListe = "SUIVI"
        frmElevesSuivi.pNo_ligne = 0
        frmElevesSuivi.ShowDialog()
        ' réaffiche la viewlist
        SuivisToScreen(LV_SUIVI.Items.Count)
    End Sub

    Private Sub BO_DELETE_PERMIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_DELETE_PERMIS.Click
        ' utilisateur autorisé à supprimer un permis ?
        If IsUserAuthorised(9) = False Then
            Exit Sub
        End If
        ' suppression
        deleteEnregistrement("eleves_permis", LV_PERMIS.SelectedItems(0).SubItems(0).Text, LV_PERMIS.SelectedItems(0).SubItems(6).Text)
        ' réaffiche la viewlist
        PermisToScreen(LV_PERMIS.SelectedItems(0).Index)
    End Sub

    Private Sub BO_ADD_PERMIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ADD_PERMIS.Click
        szSQL = "CALL spINS_ELEVES_PERMIS(" & gCD_CLI & ",'" & gUserCode & "')"
        If ExecQuery(ctDSN_AEM, szSQL) Then
            ' réaffiche la viewlist
            PermisToScreen(0)
        End If
    End Sub

    Private Sub BO_LETTRER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_LETTRER.Click
        ' lettrer les écritures du compte
        frmElevesLettrer.ShowDialog()
        ComptesToScreen(LV_COMPTE.SelectedItems(0).Index)
    End Sub

    Private Sub BO_MONITEURS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_MONITEURS.Click

        ' utilisateur autorisé à modifier ?
        If IsUserAuthorised(6) = True Then
            'affiche la liste des Moniteurq pour sélection
            frmTablesSearch.pTable = "moniteurs_aff"
            frmTablesSearch.pTitre = "Sélectionner un MONITEUR"
            frmTablesSearch.pCode = TE_CD_MON.Text
            frmTablesSearch.pLibelle = ""
            frmTablesSearch.ShowDialog()
            If frmTablesSearch.pCode.Length Then
                TE_CD_MON.Text = frmTablesSearch.pCode
            End If
        End If

    End Sub

    Private Sub BO_FICHE_ENREGISTRER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_FICHE_ENREGISTRER.Click
        ' enregistrer la fiche
        If SreenToFiche() = True Then
            MsgBox("Fiche enregistrée")
            ' OK, réafficher les compte
            ComptesToScreen(0)
        End If
    End Sub

    Private Sub BO_FICHE_IMPRIMER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_FICHE_IMPRIMER.Click
        ' Editions des états de la rubrique
        FrmEditions.pRubrique = "ELEVE"
        FrmEditions.pParametres = TE_FORMULE.Text
        FrmEditions.ShowDialog()
    End Sub

    Private Sub BO_ADD_PAYEUR_Click(sender As System.Object, e As System.EventArgs) Handles BO_ADD_PAYEUR.Click
        frmElevesSuivi.pListe = "PAYEUR"
        frmElevesSuivi.pNo_ligne = 0
        frmElevesSuivi.ShowDialog()
        ' réaffiche la viewlist
        PayeurToScreen(LV_PAYEUR.Items.Count)

    End Sub

    Private Sub LV_PAYEUR_DoubleClick(sender As Object, e As System.EventArgs) Handles LV_PAYEUR.DoubleClick
        If LV_PAYEUR.SelectedItems.Count > 0 Then
            ' afficher les détails de la ligne NoLigne
            frmElevesSuivi.pListe = "PAYEUR"
            frmElevesSuivi.pNo_ligne = LV_PAYEUR.SelectedItems(0).SubItems(5).Text
            frmElevesSuivi.ShowDialog()
            ' réaffiche la viewlist
            PayeurToScreen(LV_PAYEUR.SelectedItems(0).Index)
        End If
    End Sub

    Sub InitComboStatuts(ByVal combo As ComboBox)

        ' vider le combobox
        combo.Items.Clear()
        ' Lire la table STATUTS
        szSQL = "CALL spGet_P_TABLES_byCODE('P_STATUTS',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            combo.Items.Add(Format(rs.Fields("code").Value, "00") & " | " & rs.Fields("libelle").Value)
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()
        combo.SelectedIndex = 0

    End Sub


End Class